package edu.mju.dao.impl;

import edu.mju.dao.IRoutesDao;
import edu.mju.entity.ScenicSpot;
import edu.mju.entity.TouristRoutes;
import edu.mju.entity.User;
import edu.mju.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wyp
 * @Date:2020/5/25 11:02
 */
public class RoutesDaoImpl implements IRoutesDao {
    Connection conn = null;
    PreparedStatement preparedStatement = null ;

    public PreparedStatement preparedStatement(String sql){
        try {
            conn  = MySqlDbUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("UserDaoImpl>preparedStatement方法出错");
        }
        return preparedStatement;
    }

    @Override
    public boolean delete(Integer routeId) {
        boolean delete = false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "DELETE FROM TouristRoutes WHERE touristId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,routeId);
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    @Override
    public boolean insert(TouristRoutes routes) {
        boolean insert= false;
        try {
            String sql = "INSERT INTO TouristRoutes(allSpot,restaurant,live,traffice,touristTime,touristPrice,tel,touristCompanyId,maxPeople,routesImg) VALUES (?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = preparedStatement(sql);
            if(pstmt != null){
                pstmt.setString(1,routes.getAllSpot());
                pstmt.setString(2,routes.getRestaurant());
                pstmt.setString(3,routes.getLive());
                pstmt.setString(4,routes.getTraffice());
                pstmt.setString(5,routes.getTouristTime());
                pstmt.setInt(6,routes.getTouristPrice());
                pstmt.setString(7,routes.getTel());
                pstmt.setInt(8,routes.getTouristCompanyId());
                pstmt.setInt(9,routes.getMaxPeople());
                pstmt.setString(10,routes.getRoutesImg());
                int result =pstmt.executeUpdate();
                if(result>0){
                    insert = true;
                }
            }
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return insert;
    }

    /**
     * 对应routes-edit.jsp的更新操作
     * @param routes 传入线路ID
     * @return 更新成功返回true
     */
    @Override
    public boolean update(TouristRoutes routes) {
        boolean update = false;
        try {
            Connection conn=MySqlDbUtil.getConnection();
            String sql = "UPDATE TouristRoutes SET allSpot=?,restaurant=?,live=?,traffice=?,touristTime=?,touristPrice=?,tel=?,maxPeople=?,routesImg=? WHERE touristId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,routes.getAllSpot());
            pstmt.setString(2,routes.getRestaurant());
            pstmt.setString(3,routes.getLive());
            pstmt.setString(4,routes.getTraffice());
            pstmt.setString(5,routes.getTouristTime());
            pstmt.setInt(6,routes.getTouristPrice());
            pstmt.setString(7,routes.getTel());
            pstmt.setInt(8,routes.getMaxPeople());
            pstmt.setString(9,routes.getRoutesImg());
            pstmt.setInt(10,routes.getTouristId());
            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public Pager pagenate(int page, int pageSize, TouristRoutes routes) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from TouristRoutes ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(routes != null) {
            if (routes.getAllSpot() != null && !"".equals(routes.getAllSpot())) {
                where.append("AND allSpot like ? ");
                params.add("%" + routes.getAllSpot() + "%");
            }
            if (routes.getTouristTime() != null && !"".equals(routes.getTouristTime())) {
                where.append("AND touristTime like ? ");
                params.add("%" + routes.getTouristTime() + "%");
            }
            if (routes.getTouristPrice() != null && !"".equals(routes.getTouristPrice()) ) {
                where.append("AND touristPrice <= ? ");
                params.add(routes.getTouristPrice());
            }
            if(routes.getTouristCompanyId()!=null && !"".equals(routes.getTouristCompanyId())){
                where.append("AND touristCompanyId = ? ");
                params.add(routes.getTouristCompanyId());
            }

        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from TouristRoutes ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()){
                routes = new TouristRoutes(rs.getInt("touristId"),rs.getString("allSpot"),
                        rs.getString("restaurant"),rs.getString("live"),rs.getString("traffice"),
                        rs.getString("touristTime"),rs.getInt("touristPrice"),
                        rs.getString("tel"),rs.getInt("touristCompanyId"),
                        rs.getInt("maxPeople"),rs.getInt("nowPeople"),rs.getString("routesImg"));
                data.add(routes);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }

    @Override
    public Pager pagenateByCompanyId(int page, int pageSize, TouristRoutes routes) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from TouristRoutes ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(routes != null) {
            if(routes.getTouristCompanyId()!=null && !"".equals(routes.getTouristCompanyId())){
                where.append("AND touristCompanyId = ? ");
                params.add("" + routes.getTouristCompanyId() + "");
            }
            if (routes.getAllSpot() != null && !"".equals(routes.getAllSpot())) {
                where.append("AND allSpot like ? ");
                params.add("%" + routes.getAllSpot() + "%");
            }
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from TouristRoutes ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()){
                routes = new TouristRoutes(rs.getInt("touristId"),rs.getString("allSpot"),
                        rs.getString("restaurant"),rs.getString("live"),rs.getString("traffice"),
                        rs.getString("touristTime"),rs.getInt("touristPrice"),
                        rs.getString("tel"),rs.getInt("touristCompanyId"),
                        rs.getInt("maxPeople"),rs.getInt("nowPeople"),rs.getString("routesImg"));
                data.add(routes);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }

    @Override
    public TouristRoutes findByTouristId(Integer touristId) {
        TouristRoutes routes = new TouristRoutes();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM TouristRoutes where touristId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,touristId);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                routes.setTouristId(rs.getInt("touristId"));
                routes.setAllSpot(rs.getString("allSpot"));
                routes.setRestaurant(rs.getString("restaurant"));
                routes.setLive(rs.getString("live"));
                routes.setTraffice(rs.getString("traffice"));
                routes.setTouristTime(rs.getString("touristTime"));
                routes.setTouristPrice(rs.getInt("touristPrice"));
                routes.setTel(rs.getString("tel"));
                routes.setTouristCompanyId(rs.getInt("touristCompanyId"));
                routes.setMaxPeople(rs.getInt("maxPeople"));
                routes.setNowPeople(rs.getInt("nowPeople"));
                routes.setRoutesImg(rs.getString("routesImg"));
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return routes;
    }
}
